#include "head.h"

sqlite3 *db = NULL;  //数据库句柄

/**
 ** @author:	  王辰
 ** @date:        2016.12.27
 ** @brief:       登录
 */
void do_login(int sockfd, MSG *pbuf){
	char cmd[256] = {};
	char *errmsg;
	char **resultp;
	int n_row, n_cloum;
	int j;

	if (sqlite3_open(DATABASE, &db) != SQLITE_OK) {
		printf("%s", sqlite3_errmsg(db));
		exit(EXIT_FAILURE);
	}

	sprintf(cmd, "select * from admin where name = '%s'  and pass = '%s'", pbuf->name, pbuf->passwd);
	
	if(sqlite3_get_table(db, cmd, &resultp, &n_row, &n_cloum, &errmsg) !=  SQLITE_OK) {
		printf("error:%s\n",  errmsg);	
	}else {
		if (n_row == 0){
			memset(cmd, 0, sizeof(cmd));
			sprintf(cmd, "select * from staff where name = '%s'  and pass = '%s'", pbuf->name, pbuf->passwd);

			if(sqlite3_get_table(db, cmd, &resultp, &n_row, &n_cloum, &errmsg) !=  SQLITE_OK) {
				printf("error:%s\n",  errmsg);
			} else {
				if (n_row > 0) {
					pbuf->ret = STAFF;
					pbuf->type = SUCCESS;

					send(sockfd, pbuf, sizeof(MSG), 0);
				} else if (n_row == 0) {
					pbuf->type = FAILED;

					send(sockfd, pbuf, sizeof(MSG), 0);
				}
			}
		} else if (n_row > 0) {
			pbuf->ret = ADMIN;
			pbuf->type = SUCCESS;

			printf("%s login\n",pbuf->name);
			send(sockfd, pbuf, sizeof(MSG), 0);
		}
	}
}
 
 /**
 * @brief 服务器端调用该函响应admin，staff请求修改自己的密码，接收客户端发送的新密码，
 *        并更改数据库密码，返回SUCCESS给客户端。
 * @param 
 * @return 
 */ 
 /**
 ** @author:	  王陈陈
 ** @date:        2016.12.27
 ** @brief:       管理员，员工修改密码
 */
void do_staff_change_passwd(int sockfd, MSG *pbuf){
	printf("start to change passwd\n");
	recv(sockfd,pbuf,sizeof(MSG),0);
	printf("recv new passwd ok\n");
	
	char *errmsg;
	char cmd[256];
	
	if(pbuf->ret == ADMIN){
		sprintf(cmd, "update admin set pass = '%s' where name = '%s' ",pbuf->passwd,pbuf->name);
		
		printf("cmd=%s\n", cmd);
	}else if(pbuf->ret == STAFF){
		sprintf(cmd, "update staff set pass = '%s' where name = '%s' ",pbuf->passwd,pbuf->name);
		
		printf("cmd=%s\n", cmd);
	}
	
	if(sqlite3_exec(db, cmd, NULL, NULL, &errmsg) != SQLITE_OK){
			printf("%s :error:%s\n",pbuf->name, errmsg);
			exit(-1);
	}else{
	    printf("%s update passwd ok\n", pbuf->name);

		pbuf->ret=SUCCESS;

		send(sockfd, pbuf, sizeof(MSG), 0);
	}	
}

/**
 ** @author:	  蒯进
 ** @date:        2016.12.27
 ** @brief:       员工查询
 */
void do_staff_query(int sockfd, MSG *pbuf){
	char cmd[256];
	char *errmsg, **result;
	int nrow, ncolumn;
	
	sprintf(cmd, "select * from staff where name = '%s'", pbuf->name);
	if(sqlite3_get_table(db, cmd, &result, &nrow, &ncolumn, &errmsg) != SQLITE_OK){
		printf("error:%s\n",errmsg);
		sqlite3_free(errmsg);
	}

	if(nrow == 0){
		strcpy(pbuf->data, "name is wrong!");
	}else{

		pbuf->info.id = atoi(result[0+ncolumn]);
		strcpy(pbuf->info.name ,result[1+ncolumn]);
		pbuf->info.age = atoi(result[3+ncolumn]);
		strcpy(pbuf->info.addr,result[4+ncolumn]);
		pbuf->info.salary = atoi(result[5+ncolumn]);
		
		strcpy(pbuf->data, "ok");
	}

	send(sockfd, pbuf, sizeof(MSG), 0);
	sqlite3_free_table(result);

	return;
}

/**
 ** @author:	  孔祥锴
 ** @date:        2016.12.27
 ** @brief:       员工增加
 */
void do_admin_add(int connfd, MSG *pbuf){
	char cmd[256];
	char *errmsg;
	char **result;
	int nrow, ncolumn;

	sprintf(cmd, "insert into staff values (%d,'%s','%s',%d,'%s',%lf)",pbuf->info.id,pbuf->info.name,pbuf->passwd,pbuf->info.age,pbuf->info.addr,pbuf->info.salary);

	if(sqlite3_get_table(db, cmd, &result, &nrow, &ncolumn, &errmsg) != SQLITE_OK){
		printf("error:%s\n",errmsg);
		sqlite3_free(errmsg);
	}else{
		printf("insert over\n");
		strcpy(pbuf->data, "insert over\n");

		send(connfd,pbuf,sizeof(MSG),0); 
	}
	sqlite3_free_table(result);
}

/**
 ** @author:	  孔祥锴
 ** @date:        2016.12.27
 ** @brief:       员工删除
 */
void do_admin_del(int connfd, MSG *pbuf){
	char cmd[256]; 
	char *errmsg; 
	char **result;
	int nrow, ncolumn;

	sprintf(cmd, "delete from staff where name = '%s' and id = '%d'", pbuf->info.name,pbuf->info.id);
	
	if(sqlite3_get_table(db, cmd, &result, &nrow, &ncolumn, &errmsg) != SQLITE_OK){
		printf("error:%s\n",errmsg);
		sqlite3_free(errmsg);
	}else{
		printf("delete voer\n");
		strcpy(pbuf->data, "delete over\n");

		send(connfd,pbuf,sizeof(MSG),0); 
	}

	sqlite3_free_table(result);

	return; 
}

/**
 ** @author:	  蒯进
 ** @date:        2016.12.27
 ** @brief:       管理员查询
 */
void do_admin_query(int sockfd, MSG *pbuf){
	char cmd[256];
	char *errmsg, **result; 
	int nrow, ncolumn;

	sprintf(cmd,"select * from staff where name = '%s'",pbuf->info.name);

	if(sqlite3_get_table(db, cmd, &result, &nrow, &ncolumn, &errmsg) != SQLITE_OK){
		printf("error:%s\n",errmsg);
		sqlite3_free(errmsg);
	}

	if(nrow == 0){
		strcpy(pbuf->data, "name is wrong!");
	}else{
		pbuf->info.id = atoi(result[0+ncolumn]);
		strcpy(pbuf->info.name ,result[1+ncolumn]);
		pbuf->info.age = atoi(result[3+ncolumn]);
		strcpy(pbuf->info.addr,result[4+ncolumn]);
		pbuf->info.salary = atoi(result[5+ncolumn]);

		strcpy(pbuf->data, "ok");
	}

	send(sockfd, pbuf, sizeof(MSG), 0);
	sqlite3_free_table(result);

	return;
}

void *handler(void * arg){
     MSG msg;
    int n;
    int connfd = (int)arg;

    while(1)
    {
        n = recv(connfd, &msg, sizeof(MSG), 0);
        //printf("name = %s\n", msg.name);
        if(n == 0){  //客户端退出
            pthread_exit(NULL);
            close(connfd);
        }else if(n > 0){  //收到信息
            //printf("recv type = %d\n", msg.type);
            switch (msg.type) {
            case LOGIN:  //登录
                do_login(connfd, &msg);
                break;
            case STAFF_CHANGE_PASSWD:
                do_staff_change_passwd(connfd, &msg);
                break;
            case STAFF_QUERY:
                do_staff_query(connfd, &msg);
                break;
            case ADMIN_QUERY:
                do_admin_query(connfd, &msg);
                break;
            case ADMIN_DEL_STAFF:
                do_admin_del(connfd, &msg);
                break;
            case ADMIN_ADD_STAFF:
                do_admin_add(connfd, &msg);
                break;
            default:
                break;
            }
        }else{
            printf("abc");
            break;
        }
    }
    close(connfd);
    pthread_exit(NULL);
}

int main(int argc, char *argv[])
{
    int listenfd, connfd;
    struct sockaddr_in myaddr;

    if (argc < 3)
    {
        printf("Usage : %s <ip> <port>\n", argv[0]);
        exit(-1);
    }

    //打开数据库
    if(sqlite3_open(DATABASE, &db) < 0){
        printf("fail to sqlite3_open : %s\n", sqlite3_errmsg(db));
        return -1;
    }

    //创建服务器socket
    listenfd = socket(PF_INET, SOCK_STREAM, 0);
    if(listenfd < 0){
        perror("fail to socket");
        exit(-1);
    }
    bzero(&myaddr, sizeof(myaddr));
    myaddr.sin_family = PF_INET;
    myaddr.sin_port = htons(atoi(argv[2]));
    myaddr.sin_addr.s_addr = inet_addr(argv[1]);
    if (bind(listenfd, (SA *)&myaddr, sizeof(myaddr)) < 0)
    {
        perror("fail to bind");
        exit(-1);
    }

    // XXX int listen(int sockfd, int backlog);
    if (listen(listenfd, 5) < 0)
    {
        perror("fail to listen");
        exit(-1);
    }

    //接受客户端请求，创建子线程
    while(1){
        if((connfd = accept(listenfd, NULL, NULL)) < 0){
            perror("fail to accept");
            exit(-1);
        }
        pthread_t child_pthread;
        if(pthread_create(&child_pthread, NULL, handler,(void *)connfd) < 0)
        {
            perror("pthread_create");
            exit(1);
        }		
    }
}
